package org.example.managementsystem.repository;

import org.example.managementsystem.model.Schedule;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;
import java.util.Objects;

@Repository
public class ScheduleDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ScheduleDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Schedule> findByYearAndMonth(int year, int month) {
        String sql = "SELECT * FROM schedule WHERE YEAR(schedule_time) = ? AND MONTH(schedule_time) = ?";
        return jdbcTemplate.query(sql, new ScheduleRowMapper(), year, month);
    }

    public List<Schedule> findByYearAndMonthAndDepartment(int year, int month, String department) {
        String sql = "SELECT * FROM schedule WHERE YEAR(schedule_time) = ? AND MONTH(schedule_time) = ? AND department = ?";
        return jdbcTemplate.query(sql, new ScheduleRowMapper(), year, month, department);
    }

    public Schedule save(Schedule schedule) {
        if (schedule.getId() == null) {
            // 插入新记录
            String insertSql = "INSERT INTO schedule (job_number, schedule_time, department, is_active) VALUES (?, ?, ?, ?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, schedule.getJobNumber());
                ps.setTimestamp(2, Timestamp.valueOf(schedule.getScheduleTime()));
                ps.setString(3, schedule.getDepartment());
                ps.setBoolean(4, schedule.getIsActive());
                return ps;
            }, keyHolder);

            schedule.setId(Objects.requireNonNull(keyHolder.getKey()).longValue());
        } else {
            // 更新现有记录
            String updateSql = "UPDATE schedule SET job_number = ?, schedule_time = ?, department = ?, is_active = ? WHERE schedule_id = ?";
            jdbcTemplate.update(updateSql,
                    schedule.getJobNumber(),
                    schedule.getScheduleTime(),
                    schedule.getDepartment(),
                    schedule.getIsActive(),
                    schedule.getId());
        }
        return schedule;
    }

    public boolean existsById(Long id) {
        String sql = "SELECT COUNT(*) FROM schedule WHERE schedule_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }

    public void deleteById(Long id) {
        String sql = "DELETE FROM schedule WHERE schedule_id = ?";
        jdbcTemplate.update(sql, id);
    }

    public Schedule findById(Long id) {
        String sql = "SELECT * FROM schedule WHERE schedule_id = ?";
        List<Schedule> schedules = jdbcTemplate.query(sql, new ScheduleRowMapper(), id);
        return schedules.isEmpty() ? null : schedules.get(0);
    }
    
    /**
     * 根据员工工号查询排班记录
     * 
     * @param jobNumber 员工工号
     * @return 排班记录列表
     */
    public List<Schedule> findByJobNumber(String jobNumber) {
        String sql = "SELECT * FROM schedule WHERE job_number = ?";
        return jdbcTemplate.query(sql, new ScheduleRowMapper(), jobNumber);
    }

    private static final class ScheduleRowMapper implements RowMapper<Schedule> {
        @Override
        public Schedule mapRow(ResultSet rs, int rowNum) throws SQLException {
            Schedule schedule = new Schedule();
            schedule.setId(rs.getLong("schedule_id"));
            schedule.setJobNumber(rs.getString("job_number"));
            schedule.setScheduleTime(rs.getTimestamp("schedule_time").toLocalDateTime());
            schedule.setDepartment(rs.getString("department"));
            schedule.setIsActive(rs.getBoolean("is_active"));
            return schedule;
        }
    }
}